/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.siscoh.model.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Israel
 */
public class Conexao {

    private Connection con;
    private static final String HOST_DB = "localhost", PORTA_DB = "3306",
            NAME_DB = "siscoh", USUARIO_DB = "root", SENHA_DB = "";

    private Conexao() {
        con = null;
        try {
            Class.forName("com.mysql.jdbc.Driver"); 
            con = DriverManager.getConnection("jdbc:mysql://" + HOST_DB + ":" + PORTA_DB + "/" + NAME_DB,
                    USUARIO_DB, SENHA_DB);
            con.setAutoCommit(true);
        } catch (Exception e) {
            System.err.println(e.getMessage());
        }
    }

    public static Conexao getInstance() {
        return new Conexao();
    }

    public long insert(String table, String[] into, String[] values)
    {
        String query, strInto, strValue;
        int i, qtdFields = into.length;
        
        query = "INSERT INTO "+table+" ( ";
        
        strInto = "";
        strValue = " values ( ";
        
        for(i = 0; i < qtdFields-1; i++)
        {
            strInto += into[i]+", ";
            strValue += "?, ";
        }
        
        strInto += into[i]+") ";
        strValue += "?)";
        
        query += strInto+strValue;
        PreparedStatement ps = null;
        
        try {
            ps = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            System.out.println(query);
            
            i = 1;
            
            for(String s:values)
            {
                ps.setString(i, values[i-1]);
                i++;
            }
            
            
            ps.execute();
            ResultSet rs = ps.getGeneratedKeys();
            rs.next();
            
            if(rs != null)
            {
                return rs.getLong(1);
            }
            
        } catch (SQLException ex)
        {
            System.err.println(ex.getMessage());
        }
        finally
        {
            try {
                if(ps != null && !ps.isClosed())
                {
                    ps.close();
                    con.close();
                }
            } catch (SQLException ex) {}
        }
        
        return -1;
    }
    /**
     * @param values 
     * @param table 
     * @return 
     */
    public long insert(String table, ArrayList<String> into, ArrayList<String> values) {
        return insert(table,(String[]) into.toArray(), (String[]) values.toArray());
    }
    
    public long insert(String table, String[] into, ArrayList<String> values)
    {
        return insert(table, into, (String[]) values.toArray());
    }

    /**
     * @param value 
     * @param column 
     * @param table 
     * @param where 
     */
    public void update(String table, String[] column, String[] value, String where) {
        if(where == null)
            where = "";
        else
            where = "WHERE "+where;
        
        String updateStr = "";
        
        int i;
        for(i = 0; i < (column.length-1); i++)
        {
            updateStr += column[i]+" = '"+value[i]+"', ";
        }
        
        updateStr += column[i]+" = '"+value[i]+"'";
        
        try {
            PreparedStatement ps = con.prepareStatement("UPDATE "+table+" SET "+updateStr+" "+where);
            ps.execute();
        } catch (SQLException ex) {
            ex.printStackTrace(System.err);
        }
        
    }

    /**
     * @param select 
     * @param table 
     * @param where 
     * @return 
     */
    public ResultSet select(String select, String table, String where) {
        
        if(select == null || select.equals(""))
                select = "*";
        
        if(where == null || where.equals(""))
            where = "";
        else
            where = " WHERE "+where;
        
        ResultSet rs = null;
        
        try {
            
            PreparedStatement ps = con.prepareStatement("SELECT "+select+" FROM "+table+where);
            
            System.err.println("SELECT "+select+" FROM "+table+where);
            
            rs = ps.executeQuery();
            
        } catch (SQLException ex) {
            System.err.println(ex.getMessage());
            return null;
        }
        
        
        return rs;
    }
    public ResultSet select(String select, String table)
    {
        return select(select, table, "");
    }
    
    /**
     * @param select 
     * @param table 
     * @param where 
     * @return 
     */
    public ResultSet select(String select, String table, String where, String extras) {
        
        if(select == null || select.equals(""))
                select = "*";
        
        if(where == null || where.equals(""))
            where = "";
        else
            where = " WHERE "+where;
        
        if(extras == null)
            extras = "";
        
        ResultSet rs = null;
        
        try {
            
            System.out.println("Sql do select => "+"SELECT "+select+" FROM "+table+where+" "+extras);
            
            PreparedStatement ps = con.prepareStatement("SELECT "+select+" FROM "+table+where+" "+extras);
            
            rs = ps.executeQuery();
            
        } catch (SQLException ex) {
            System.err.println(ex.getMessage());
            return null;
        }
        
        
        return rs;
    }

    /**
     * @param query 
     */
    public void customQuery(String query) {
        try {
            
            PreparedStatement ps = con.prepareStatement(query);
            
            ps.execute();
            
        } catch (SQLException ex) {
            System.err.println(ex.getMessage());
        }
    }

    /**
     * @param query 
     * @return 
     */
    public ResultSet customSelect(String query) {
        ResultSet rs = null;
        
        try {
            
            PreparedStatement ps = con.prepareStatement(query);
            
            rs = ps.executeQuery();
            
        } catch (SQLException ex) {
            System.err.println(ex.getMessage());
            return null;
        }
        
        
        return rs;
    }

    /**
     * @param id 
     * @param table 
     */
    public void delete(String table, long id) {
    }

    /**
     * @param table 
     * @param where 
     */
    public void delete(String table, String where) {
    }
}
